package com;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelImport2 {

    public static void main(String[] args) throws FileNotFoundException {
        String fileName = "G:\\IdeaProjects\\my-java-project\\dc-java\\src\\test\\java\\11.xlsx";
        List<List> allExcel = getAllExcel(fileName);
        for (int i = 0; i <allExcel.size() ; i++) {
            List xx= allExcel.get(i);
            for (int j = 0; j <xx.size() ; j++) {

            }
        }
        System.out.println(allExcel);
//        new ExcelImport2().importExcel(fileName);
    }

    public String importExcel(String fileName) throws FileNotFoundException {

        FileInputStream input = new FileInputStream(fileName);

        String message = "Import success";
        boolean isE2007 = false;
        //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            isE2007 = true;
        }

        int rowIndex = 0;
        try {
            Workbook wb;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            Sheet sheet = wb.getSheetAt(1);

            int beginRow = ExcelUtil.getRowNum(wb, "tews");
            int endRow = ExcelUtil.getRowNum(wb, "xxx");
            List<FightPrice> list = new ArrayList<>();

            log.info("t2_begin={},t2_end={}", beginRow, endRow);

            for (int i = beginRow; i < endRow + 1; i++) {
                rowIndex = i;
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                int cols = row.getLastCellNum();
                for (int j = 2; j < cols; j++) {
                    if (ExcelUtil.isMergedRegion(sheet, i, j)) {
                        System.out.print(ExcelUtil.getMergedRegionValue(sheet, i, j) + "|\t");
                    } else {
                        row = sheet.getRow(i);
                        System.out.print(row.getCell(j) + "|\t");
                    }
                }
                System.out.print("\n");
            }
//            log.info("===>{}", list);
        } catch (Exception ex) {
            message = "Import failed, please check the data in " + rowIndex + " rows ";
            System.out.println(ex.getMessage());
        }
        return message;
    }


    public static List getAllExcel(String fname) {


        List listt = new ArrayList();

        try {
            FileInputStream fis = new FileInputStream(new File(fname));
            Workbook workbook = null;
            if (fname.toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (fname.toLowerCase().endsWith("xls")) {
                workbook = new HSSFWorkbook(new POIFSFileSystem(fis));
            }
//            int numberOfSheets = workbook.getNumberOfSheets();
//
//            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(1);
                for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每行
                    XSSFRow row = (XSSFRow) sheet.getRow(j);
                    if (row != null) {
                        List list = new ArrayList();
                        for (int k = 1; k < sheet.getRow(0).getPhysicalNumberOfCells(); k++) { // 获取每个单元格
                            Cell cell = row.getCell(k);
                            if (cell == null) {
                                list.add("");
                                continue;
                            }
                            switch (cell.getCellType()) {
                                case STRING:
                                    list.add(cell.getRichStringCellValue().getString());
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        list.add(cell.getDateCellValue());
                                    } else {
                                        list.add(cell.getNumericCellValue());
                                    }
                                    break;
                                case BOOLEAN:
                                    list.add(cell.getBooleanCellValue());
                                    break;
                                case FORMULA:
                                    list.add(cell.getCellFormula());
                                    break;
                                default:
                                    list.add("");
                                    break;
                            }
                        }

                        listt.add(list);
                    }
                }
//            }
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return listt;
    }


}
